"""
TODO 交叉连接查询

1、介绍：返回被链接的两个表所有数据行的笛卡尔积
        一张表的每一行去匹配另一张表的任意一行
        （A表有 m 行，B表有 n 行，返回 m * n 行）
2、语法：
     select * from student,course;

TODO 内连接查询

1、介绍：查询多张表的交集
2、语法：
 查询每个部门的所属员工：
     ① 隐式内连接：
          select * from dept,emp where dept.deptno = emp.dept_id;
     ② 显示内连接：
          select * from dept (inner) join emp on dept.deptno = emp.dept_id;
 查询研发部门的所属员工：
     ① 隐式内连接：
          select * from dept,emp where dept.deptno = emp.dept_id and name = '研发部';
     ② 显示内连接：
          select * from dept join emp on dept.deptno = emp.dept_id and name = '研发部';
 查询每个部门的员工数，并升序排序：
   select name,count(*) c from dept join emp on dept_id = deptno group by (dept.deptno / emp.dept_id / name) order by c;
 查询人数大于等于3的部门，并按人数降序排序：
   select name,count(*)/count(1) as c from dept join emp on deptno = dept_id group by name having c>= 3 order by c desc;

TODO 外连接查询

1、介绍：满外连接，可以用union代替
2、语法：
  查询哪些部门有员工：
     select * from dept left (outer) join emp on deptno = dept_id;
  查询哪些员工有对应的部门：
     select * from dept right join emp on deptno = dept_id;
  使用union关键字实现左外连接和右外连接的并集：
     select * from dept left (outer) join emp on deptno = dept_id
     union
     select * from dept right (outer) join emp on deptno = dept_id;

TODO 子查询

1、介绍：在一个完整的查询语句中，嵌套若干个不同功能的小查询，即包含select嵌套的查询
2、返回的数据类型：
    ① 单行单列：一个单值数据
    ② 单行多列：一行
    ③ 多行单列：一列
    ④ 多行多列：一张临时表
3、语法：
  查询年龄最大的员工信息，包含员工工号、名字、年龄：
      select * from emp where age = (select max(age) from emp);     --- 单行单列
  查询研发部和销售部的员工信息，包含员工工号、名字：
   ① 内连接查询：
         select * from emp join dept on deptno = dept_id and name in ('销售部','研发部');
   ② 子查询：
         select * from emp where dept_id in(select deptno from dept where name in ('生产部','研发部'));
  查询研发部20岁以下的员工信息，包含员工工号、名字，部门名字：
     ① 内连接查询：
         select * from emp join dept on deptno = dept_id and (name = '研发部' and age < 20)
     ② 子查询：
         select * from
         (select * from dept where name = '研发部') t1
         join
         (select * from emp where age < 20) t2
         on t1.deptno =t2.dept_id;
4、关键字：
  ① all：
      比all()内的所有值都要大
       查询年龄大于'1003'部门所有年龄的员工信息：
           select * from emp where age > all(select age from emp where dept_id ='1003');
       查询不属于任何一个部门的员工信息：
           select * from emp where dept_id != all(select deptno from dept);
  ② any 和 some：作用一模一样
     比any/some()内的任意一个值大
       查询年龄大于'1003'部门任意一个员工年龄的员工信息：
           select * from emp where age > any/some(select age from emp where dept_id = '1003') and dept_id != '1003';
  ③ in：
      是否为in()内的任意一个值
        查询研发部和销售部的员工信息，包含员工号、姓名：
           select * from emp where dept_id in(select deptno from dept where name in('研发部','销售部'));
  ④ exists(查询语句)：
       查询语句返回至少一行数据或者返回真，exists为True，外层查询执行
         查询是否有大于60岁的员工，有则输出：
           (select * from emp) a where exists(select * from emp where a.age > 60);
           或：
           select * from emp a where eid in(select * from emp where a.age > 60);
         全表输出：
           select * from emp where exists(1);
           select * from emp where exists(select * from emp);
         查询所属部门的员工信息：
           select * from emp a where exists(select * from dept b where a.dept_id = b.deptno);
           或：
           select * from emp a where dept_id in(select deptno from dept b where a.dept_id = b.deptno);

TODO 自关联查询

1、介绍：一张表和自己关联，当成多张表使用
       自关联时表必须起别名
2、语法：
     创建表：
       use mydb;
       create table sanguo(
       eid int primary key,
       ename varchar(20),
       manager_id int,
       foreign key(manager_id) references sanguo(eid)
       );
     查询每个三国人物及他的上级信息：
         select a.ename, b.ename from sanguo a, sanguo b where a.manager_id = b.eid;
         或：
         select a.ename as a,b.ename as b from sanguo a join sanguo b on a.eid = b.manager_id;
     查询所有人物及上级：
         select a.ename as a,b.ename as b from sanguo a left join sanguo b on a.eid = b.manager_id;
     查询所有人物、上级、上上级：
         select a.ename a,b.ename b,c.ename c from sanguo a left join sanguo b on a.eid = b.manager_id
                                                            left join sanguo c on b.eid = c.manager_id;


综合案例：

-- 创建部门表
create table dept(
deptno int primary key,    -- 部门编号
dname varchar(20),  -- 部门名称
loc varchar(20) -- 部门地址
);
-- 创建员工表
create table emp(
empno int primary key, -- 员工编号
ename varchar(20), -- 员工姓名
job varchar(20), -- 员工工作
mgr int, -- 员工直属领导编号
hiredate date, -- 入职时间
sal double,-- 工资
comm double, -- 奖金
deptno int -- 对应dept表的外键
);
-- 添加外键
alter table emp add foreign key(deptno) references dept(deptno);
-- 创建工资登记表
create table salg(
grade int,  -- 等级
losal double, -- 最低工资
hisal double -- 最高工资
);
-- 返回拥有员工的部门名、部门号
select distinct emp.deptno,dname from emp join dept on emp.deptno = dept.deptno;
-- 工资水平多于smi的员工信息
select * from emp where sal > (select sal from emp where ename = 'smi');
-- 返回员工和所属经理的姓名
select a.ename an,b.ename bn from emp a join emp b on a.empno = b.mgr;
-- 返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
select a.ename an,b.ename bn from emp a join emp b on a.empno = b.mgr and a.hiredate < b.hiredate;
-- 返回员工姓名及所在部门名称
select ename,dname from emp join dept on emp.deptno = dept.deptno;
-- 返回从事cle工作的员工姓名和所在部门名称
select ename,dname from emp join dept on emp.deptno = dept.deptno and emp.job = 'cle';
-- 返回部门号及本部门的最低工资
select deptno,min(sal) from emp group by deptno;
-- 返回销售部（sal）所有员工的姓名
select ename from emp join dept on emp.deptno = dept.deptno and dname = 'sales';
-- 返回工资水平多于平均工资的员工
select * from  emp where sal > (select avg(sal)from emp);
-- 返回与sco从事相同工作的员工
select * from emp where job = (select job from emp where ename = 'sco') and ename != 'sco';
-- 返回与30部门员工工资水平相同的员工姓名和工资
select * from emp where sal =all(select sal from emp where deptno = 30);
-- 返回员工工作及其从事此工作的最低工资
select job,min(sal) from emp group by job;
--计算员工的年薪，并按年薪排序
select *,sal*12+ifnull(comm,0) as total_sal from emp order by total_sal;
-- 返回工资处于第四级别的员工姓名
select * from emp where sal between (select losal from salg where grade = 4) and (select hisal from salg where grade = 4) ;
-- 返回工资为二等级的员工姓名、部门所在地
select loc,ename from emp join dept on emp.deptno = dept.deptno and sal between(select losal from salg where grade = 2)
                                                                       and (select hisal from salg where grade =2);
或1：（join salg 后没加条件是硬拼在一起的）
select * from emp join dept on emp.deptno =dept.deptno join salg on grade=2 and emp.sal>salg.losal and emp.sal < hisal;
或2：
select * from emp,dept,salg where emp.deptno =dept.deptno and grade = 2 and emp.sal >= salg.losal and emp.sal <= hisal;
公交车：
select distinct bus1.num,bus1.company, name,bus2.num,bus2.company
from
(select r1.num num,r1.company company ,r2.stop stop from
route r1 join route r2 on
r1.num = r2.num
and r1.company = r2.company
and r1.stop != r2.stop
where r1.stop = (select id from stops where name = 'Craiglockhart')) bus1
join
(select r1.num num,r1.company company,r1.stop stop from
route r1 join route r2 on
r1.num = r2.num
and r1.company = r2.company
and r1.stop != r2.stop
where r2.stop = (select id from stops where name = 'Lochend')) bus2
on bus1.stop = bus2.stop
join stops st on
bus1.stop = st.id
"""